﻿using HRM.Model;
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HRM.Helper
{
    public class ExportExcelSalary:IExportExcel<BangLuong>
    {

        public void ExportExel(List<BangLuong> items, string sheetName, string title)
        {
            using (HRMEntities entities = new HRMEntities())
            {
                Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
                Workbooks workbooks;
                Sheets sheets;
                Workbook workbook;
                Worksheet worksheet;
                oExcel.Visible = true;
                oExcel.DisplayAlerts = false;

                oExcel.Application.SheetsInNewWorkbook = 1;
                workbooks = oExcel.Workbooks;

                workbook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
                sheets = workbook.Worksheets;
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
                worksheet.Name = sheetName;
                // Tạo header cho báo cáo
                Microsoft.Office.Interop.Excel.Range head = worksheet.get_Range("A1", "F1");
                head.MergeCells = true;
                head.Value2 = title;
                head.Font.Bold = true;
                head.Font.Name = "Tahoma";
                head.Font.Size = "18";
                head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                // Tạo header cho report
                Microsoft.Office.Interop.Excel.Range cl1 = worksheet.get_Range("A3", "A3");
                cl1.Value2 = "ID";
                cl1.ColumnWidth = 13.5;
                Microsoft.Office.Interop.Excel.Range cl2 = worksheet.get_Range("B3", "B3");
                cl2.Value2 = "NameStaff";
                cl2.ColumnWidth = 25.0;
                Microsoft.Office.Interop.Excel.Range cl3 = worksheet.get_Range("C3", "C3");
                cl3.Value2 = "Salary";
                cl3.ColumnWidth = 25.0;

                Microsoft.Office.Interop.Excel.Range cl4 = worksheet.get_Range("D3", "D3");
                cl4.Value2 = "Month";
                cl4.ColumnWidth = 30.0;
                Microsoft.Office.Interop.Excel.Range cl5 = worksheet.get_Range("E3", "E3");
                cl5.Value2 = "Unit";
                cl5.ColumnWidth = 30.0;
                Microsoft.Office.Interop.Excel.Range cl6 = worksheet.get_Range("F3", "F3");
                cl6.Value2 = "CMND";
                cl6.ColumnWidth = 30.0;
                
                Microsoft.Office.Interop.Excel.Range rowHead = worksheet.get_Range("A3", "F3");
                rowHead.Font.Bold = true;
                // Kẻ viền
                rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
                // Thiết lập màu nền
                rowHead.Interior.ColorIndex = 15;
                rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //use object storage data
                BangLuong nv = new BangLuong();
                //get number attribute of NhanVien
                int columns = typeof(BangLuong).GetProperties().Count();

                int rows = items.Count;
                object[,] arr = new object[rows, columns];
                for (int r = 0; r < rows; r++)
                {

                    arr[r, 0] = items[r].MaBangLuong;
                    arr[r, 1] = 
                    arr[r, 2] = items[r].Thang;
                    arr[r, 3] = items[r].TienLuong;
                    arr[r, 4] = entities.NhanViens.Where(x => x.MaNhanVien == items[r].MaNV).FirstOrDefault().HoTen;
                    arr[r, 5] = items[r].DonVi;
                    
                }
                int rowStart = 4;
                int columnStart = 1;

                int rowEnd = rowStart + items.Count - 1;
                int columnEnd = columns;

                // Ô bắt đầu điền dữ liệu
                Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
                // Ô kết thúc điền dữ liệu
                Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
                // Lấy về vùng điền dữ liệu
                Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

                //Điền dữ liệu vào vùng đã thiết lập
                range.Value2 = arr;

                //range.Value2 = (System.Data.DataTable)li;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
                // Căn giữa cột STT
                Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnStart];
                Microsoft.Office.Interop.Excel.Range c4 = worksheet.get_Range(c1, c3);
                worksheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            }
        }
    }
}
